﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core;
using Kingdee.BOS.Util;
using Kingdee.BOS.WebApi.Client;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LiMinCRMPlugin
{
    /*
     查询当前组织上一期所有供应商union应付单本期所有供应商unio其他应付单、付款单、付款退款单商所有供应商，批量创建单据头
     */
    [Description("查询当前组织上一期所有供应商union应付单本期所有供应商unio其他应付单、付款单、付款退款单商所有供应商，批量创建单据头")]
    [HotUpdate]
    public class SupplierSchedule : IScheduleService
    {
        /*金蝶第三方授权登录信息-利民*/
        public static string kdUrl = "http://10.10.11.102/K3Cloud/";      /*金蝶内网URL*/
        public static string kddbid = "6642ecc879eb25";                   /*金蝶账套ID: 利民集团:6642ecc879eb25*/
        public static string kdUserName = "Administrator";                /*金蝶登录用户名*/
        public static int kdlcid = 2052;
        public static string kdappId = "276703_1/8Iwwsv2vGe2/zHX0xsTaTJ0qQ85ooo";/*金蝶第三方授权应用ID*/
        public static string kdappSecret = "523a6ff232f9483a9a7a1dc7bba4e0cf";   /*金蝶第三方授权应用秘钥*/

        //public static string kdUrl = "http://10.10.11.102/K3Cloud/";      /*金蝶内网URL */
        //public static string kddbid = "667bfe2d5ad944";                 /*金蝶账套ID: ceshi0626:667bfe2d5ad944*/
        //public static string kdUserName = "Administrator";                /*金蝶登录用户名*/
        //public static int kdlcid = 2052;
        //public static string kdappId = "276703_1/8Iwwsv2vGe2/zHX0xsTaTJ0qQ85ooo";/*金蝶第三方授权应用ID*/
        //public static string kdappSecret = "523a6ff232f9483a9a7a1dc7bba4e0cf";   /*金蝶第三方授权应用秘钥*/
        public void Run(Context ctx, Schedule schedule)
        {
            // 获取当前日期  
            DateTime now = DateTime.Now;

            // 获取上个月  
            DateTime lastMonth = now.AddMonths(-1);
            //获取上上个月  
            DateTime lastLastMonth = now.AddMonths(-2);

            // 获取上个月的年月  
            int lastMonthYear = lastMonth.Year;
            int lastMonthMonth = lastMonth.Month;
            // 获取上上个月的年月  
            int lastLastMonthYear = lastLastMonth.Year;
            int lastLastMonthMonth = lastLastMonth.Month;
            K3CloudApiClient client = new K3CloudApiClient(kdUrl);
            string loginResult = client.LoginByAppSecret(kddbid, kdUserName, kdappId, kdappSecret, kdlcid);/*第三方授权登录*/
            var result = JObject.Parse(loginResult)["LoginResultType"].Value<int>();
            if (result == 1)
            {
                //取上期的供应商阅读应付余额表的组织和供应商与本期的应付单(财务&暂估)，其他应付单，付款单，付款退款单进行合格的数据
                string sql = string.Format(@"/*dialect*/exec DEV_P_SupplierCash '{0}','{1}'", lastLastMonthYear, lastLastMonthMonth);
                DataTable dt1 = DBUtils.ExecuteDataSet(ctx, sql).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    //循环执行供应商阅读应付余额表的保存操作，创建单据头数据
                    for (int i = 0; i < dt1.Rows.Count; i++)
                    {
                        string FSETTLEORGID = dt1.Rows[i]["FSETTLEORGID"].ToString();//组织   
                        string FCONTACTUNIT = dt1.Rows[i]["FCONTACTUNIT"].ToString();//供应商
                        string FCURRENCYID = dt1.Rows[i]["FCURRENCYID"].ToString();//币别
                        JObject jsonRoot = new JObject();
                        //Model：表单数据包
                        JObject model = new JObject();
                        jsonRoot.Add("Model", model);
                        JObject FCreateOrgId = new JObject();
                        FCreateOrgId.Add("FOrgId", FSETTLEORGID);
                        model.Add("F_QLTK_OrgId", FCreateOrgId);// 创建组织

                        JObject Fsupplier = new JObject();
                        Fsupplier.Add("FSUPPLIERID", FCONTACTUNIT);
                        model.Add("F_QLTK_Supplier", Fsupplier);// 供应商

                        JObject FMAINBOOKSTDCURRID = new JObject();
                        FMAINBOOKSTDCURRID.Add("FCURRENCYID", FCURRENCYID);
                        model.Add("FMAINBOOKSTDCURRID", FMAINBOOKSTDCURRID);// 计算币别

                        model.Add("F_QLTK_year", lastMonthYear);//年
                        model.Add("F_QLTK_month", lastMonthMonth);//月

                        string sResult = client.Save("QLTK_SupplierAPbalance", jsonRoot.ToString());
                    }
                }
                //取上期的供应商阅读应付余额表的数据
                string sql12 = string.Format(@"select *from dev_SupplierAPB where F_QLTK_year='" + lastLastMonthYear + "' and F_QLTK_month='" + lastLastMonthMonth + "'");
                DataTable dt23 = DBUtils.ExecuteDataSet(ctx, sql12).Tables[0];
                if (dt23.Rows.Count > 0)
                {
                    for (int i = 0; i < dt23.Rows.Count; i++)
                    {
                        //将上期的供应商阅读应付余额表的财务应付金额数据更新到本期的期起初应付金额字段上
                        string sq = string.Format(@"update dev_SupplierAPB set F_QLTK_INITAMOUNT='" + dt23.Rows[i]["F_QLTK_ENDAMOUNT"] + "' where F_QLTK_year='" + lastMonthYear + "' and F_QLTK_month='" + lastMonthMonth + "' and F_QLTK_OrgId='" + dt23.Rows[i]["F_QLTK_OrgId"] + "' and F_QLTK_Supplier='" + dt23.Rows[i]["F_QLTK_Supplier"] + "' and FMAINBOOKSTDCURRID='" + dt23.Rows[i]["FMAINBOOKSTDCURRID"] + "' ");
                        //将上期的供应商阅读应付余额表的财务应付金额数据更新到本期的期起初应付金额字段上
                        DBUtils.Execute(ctx, sq);
                    }
                }
                //取本期的供应商阅读应付余额表的数据
                string sql11 = string.Format(@"select *from dev_SupplierAPB where F_QLTK_year='" + lastMonthYear + "' and F_QLTK_month='" + lastMonthMonth + "'");
                DataTable dt22 = DBUtils.ExecuteDataSet(ctx, sql11).Tables[0];
                if (dt22.Rows.Count > 0)
                {
                    for (int i = 0; i < dt22.Rows.Count; i++)
                    {
                        string F_QLTK_OrgId = dt22.Rows[i]["F_QLTK_OrgId"].ToString();//组织   
                        string F_QLTK_Supplier = dt22.Rows[i]["F_QLTK_Supplier"].ToString();//供应商
                        string FID = dt22.Rows[i]["FID"].ToString();//供应商
                        string FMAINBOOKSTDCURRID = dt22.Rows[i]["FMAINBOOKSTDCURRID"].ToString();//供应商


                        //计算应付单-本期应付发生额（暂估应付）
                        string sql1 = string.Format(@"/*dialect*/select FSETTLEORGID,FSUPPLIERID,FCURRENCYID,sum(FALLAMOUNTFOR) as sum from T_AP_PAYABLE 
                                    where  FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) and FSetAccountType='2'  and FDOCUMENTSTATUS='C'
                                    and FSETTLEORGID='" + F_QLTK_OrgId + "' and FSUPPLIERID='" + F_QLTK_Supplier + "' and FCURRENCYID='" + FMAINBOOKSTDCURRID + "'  GROUP BY FSUPPLIERID,FSETTLEORGID,FCURRENCYID;");
                        DataTable dt2 = DBUtils.ExecuteDataSet(ctx, sql1).Tables[0];
                        if (dt2.Rows.Count > 0)
                        {
                            //将暂估应付的价税合计本位币更新到本期的暂估应付金额上
                            DBUtils.Execute(ctx, "update dev_SupplierAPB set F_QLTK_AMOUNTZAP='" + dt2.Rows[0]["sum"] + "' where FID='" + FID + "'");
                        }

                        //计算应付单-本期应付发生额（财务应付）
                        string sql2 = string.Format(@"/*dialect*/select FSETTLEORGID,FSUPPLIERID,FCURRENCYID,sum(FALLAMOUNTFOR) as sum from T_AP_PAYABLE 
                                    where 
                                    FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))  
                                    AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) and FSetAccountType in( '3','1')  and FDOCUMENTSTATUS='C'
                                    and FSETTLEORGID='" + F_QLTK_OrgId + "' and FSUPPLIERID='" + F_QLTK_Supplier + "'  and FCURRENCYID='" + FMAINBOOKSTDCURRID + "'  GROUP BY FSUPPLIERID,FSETTLEORGID,FCURRENCYID;");
                        DataTable dt3 = DBUtils.ExecuteDataSet(ctx, sql2).Tables[0];
                        if (dt3.Rows.Count > 0)
                        {
                            //将财务应付的价税合计本位币更新到本期的财务应付金额上
                            DBUtils.Execute(ctx, "update dev_SupplierAPB set F_QLTK_AMOUNTCAP='" + dt3.Rows[0]["sum"] + "' where  FID='" + FID + "'");
                        }
                        //计算其他应付单-其他应付总金额
                        string sql3 = string.Format(@"/*dialect*/select FSETTLEORGID,FCONTACTUNIT,FCURRENCYID,sum(FTOTALAMOUNT) as sum from  T_AP_OTHERPAYABLE 
                                where FCONTACTUNITTYPE='BD_Supplier' and
                                FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))   
                                AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) and FDOCUMENTSTATUS='C'
                                AND  FSETTLEORGID='" + F_QLTK_OrgId + "' and FCONTACTUNIT='" + F_QLTK_Supplier + "'  and FCURRENCYID='" + FMAINBOOKSTDCURRID + "'  GROUP BY FCONTACTUNIT,FSETTLEORGID,FCURRENCYID;");
                        DataTable dt4 = DBUtils.ExecuteDataSet(ctx, sql3).Tables[0];
                        if (dt4.Rows.Count > 0)
                        {
                            //将其他应付的价付款金额本位币更新到本期的其他应付金额上
                            DBUtils.Execute(ctx, "update dev_SupplierAPB set F_QLTK_OAPAMOUNT='" + dt4.Rows[0]["sum"] + "' where  FID='" + FID + "'");
                        }
                        //计算付款单-付款总金额
                        string sql4 = string.Format(@"/*dialect*/select FSETTLEORGID,FCONTACTUNIT,FCURRENCYID,sum(FPAYTOTALAMOUNT) as sum from T_AP_PAYBILL 
                            where FCONTACTUNITTYPE='BD_Supplier' and
                            FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))  
                            AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))  and FDOCUMENTSTATUS='C'  AND  FSETTLEORGID='" + F_QLTK_OrgId + "' and FCONTACTUNIT='" + F_QLTK_Supplier + "'  and FCURRENCYID='" + FMAINBOOKSTDCURRID + "'  GROUP BY FCONTACTUNIT,FSETTLEORGID,FCURRENCYID");
                        DataTable dt5 = DBUtils.ExecuteDataSet(ctx, sql4).Tables[0];
                        if (dt5.Rows.Count > 0)
                        {
                            //将付款单的付款金额本位币更新到本期的付款金额上
                            DBUtils.Execute(ctx, "update dev_SupplierAPB set F_QLTK_PAYAMOUNT='" + dt5.Rows[0]["sum"] + "' where  FID='" + FID + "'");
                        }

                        //计算付款退款单-付款退款总金额
                        string sql5 = string.Format(@"/*dialect*/select FSETTLEORGID,FCONTACTUNIT,FCURRENCYID,sum(FREFUNDAMOUNT_H) as sum from T_AP_REFUNDBILL
                                where FCONTACTUNITTYPE='BD_Supplier' and
                                FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))   
                                AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))  and FDOCUMENTSTATUS='C'  AND  FSETTLEORGID='" + F_QLTK_OrgId + "' and FCONTACTUNIT='" + F_QLTK_Supplier + "'  and FCURRENCYID='" + FMAINBOOKSTDCURRID + "'  GROUP BY FCONTACTUNIT,FSETTLEORGID,FCURRENCYID;");
                        DataTable dt6 = DBUtils.ExecuteDataSet(ctx, sql5).Tables[0];
                        if (dt6.Rows.Count > 0)
                        {
                            //将付款退款单的付款金额本位币更新到本期的付款退款金额上
                            DBUtils.Execute(ctx, "update dev_SupplierAPB set F_QLTK_PAYRETURNAMOUNT='" + dt6.Rows[0]["sum"] + "' where  FID='" + FID + "'");
                        }
                        //更新期末总余额=期初应付金额+财务应付+其他应付-付款+付款退款
                        DBUtils.Execute(ctx, "update dev_SupplierAPB set F_QLTK_ENDAMOUNT= F_QLTK_INITAMOUNT+F_QLTK_AMOUNTCAP+F_QLTK_OAPAMOUNT-F_QLTK_PAYAMOUNT+F_QLTK_PAYRETURNAMOUNT  where  FID='" + FID + "'");
                    }
                }

                //取上期的供应商阅读应付余额表的组织，供应商，员工与本期的应付单(财务&暂估)，其他应付单，付款单，付款退款单的组织，供应商，员工进行合格的数据
                string sql14 = string.Format(@"/*dialect*/exec DEV_P_SupplierPurchaser '{0}','{1}'", lastLastMonthYear, lastLastMonthMonth);
                DataTable dt7 = DBUtils.ExecuteDataSet(ctx, sql14).Tables[0];
                if (dt7.Rows.Count > 0)
                {
                    for (int i = 0; i < dt7.Rows.Count; i++)
                    {
                        string F_QLTK_OrgId = dt7.Rows[i]["F_QLTK_OrgId"].ToString();//组织   
                        string F_QLTK_Supplier = dt7.Rows[i]["F_QLTK_Supplier"].ToString();//供应商
                        string F_DEV_PURCHASER = dt7.Rows[i]["F_DEV_PURCHASER"].ToString();//员工
                        string FCURRENCYID = dt7.Rows[i]["FCURRENCYID"].ToString();//币别
                        var seqValue = new Kingdee.BOS.App.Core.SequenceReader(ctx).GetSequence("dev_SupplierAPBENTITY", 1).FirstOrDefault();// 服务插件用这个方法
                        string sql15 = string.Format(@"select *from dev_SupplierAPB where F_QLTK_year='" + lastMonthYear + "' and F_QLTK_month='" + lastMonthMonth + "' and F_QLTK_OrgId='" + F_QLTK_OrgId + "' and F_QLTK_Supplier='" + F_QLTK_Supplier + "' and FMAINBOOKSTDCURRID='" + FCURRENCYID + "'");
                        DataTable dt8 = DBUtils.ExecuteDataSet(ctx, sql15).Tables[0];
                        if (dt8.Rows.Count > 0)
                        {
                            string FID = dt8.Rows[0]["FID"].ToString();
                            //插入员工
                            string sql16 = string.Format(@"insert into dev_SupplierAPBENTITY (FID,FEntryID,F_QLTK_PURCHASER) values('" + FID + "','" + seqValue + "','" + F_DEV_PURCHASER + "') ");
                            DBUtils.Execute(ctx, sql16);
                        }
                    }
                }
                //查询本期的供应商月度余额表信息
                string sql18 = string.Format(@"select a.FID,a.F_QLTK_ORGID,a.F_QLTK_SUPPLIER,a.F_QLTK_YEAR,a.F_QLTK_MONTH,a.FMAINBOOKSTDCURRID,b.FEntryID,b.F_QLTK_PURCHASER from dev_SupplierAPB a
                            inner join dev_SupplierAPBENTITY b on b.FID = a.FID
                            where F_QLTK_year='" + lastMonthYear + "' and F_QLTK_month='" + lastMonthMonth + "'");
                DataTable dt26 = DBUtils.ExecuteDataSet(ctx, sql18).Tables[0];

                if (dt26.Rows.Count > 0)
                {
                    for (int j = 0; j < dt26.Rows.Count; j++)
                    {
                        //查询本期的上期供应商月度余额表信息的财务应付金额
                        string sql17 = string.Format(@"select a.FID,a.F_QLTK_ORGID,a.F_QLTK_SUPPLIER,a.F_QLTK_YEAR,a.F_QLTK_MONTH,a.FMAINBOOKSTDCURRID,b.FEntryID,b.F_QLTK_PURCHASER,b.F_QLTK_EENDAMOUNT from dev_SupplierAPB a
                            left join dev_SupplierAPBENTITY b on b.FID = a.FID
                            where F_QLTK_year='" + lastLastMonthYear + "' and F_QLTK_month='" + lastLastMonthMonth + "' and F_QLTK_ORGID='" + dt26.Rows[j]["F_QLTK_ORGID"] + "' and F_QLTK_SUPPLIER='" + dt26.Rows[j]["F_QLTK_SUPPLIER"] + "' and F_QLTK_PURCHASER='" + dt26.Rows[j]["F_QLTK_PURCHASER"] + "' and FMAINBOOKSTDCURRID='" + dt26.Rows[j]["FMAINBOOKSTDCURRID"] +"'");
                        DataTable dt25 = DBUtils.ExecuteDataSet(ctx, sql17).Tables[0];
                        if (dt25.Rows.Count > 0)
                        {
                            DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_EINITAMOUNT='" + dt25.Rows[0]["F_QLTK_EENDAMOUNT"] + "' where FEntryID='" + dt26.Rows[j]["FEntryID"] + "'");
                        }
                    }
                }

                //查询本期的供应商月度应付月数据
                string sql13 = string.Format(@"select a.FID,a.F_QLTK_ORGID,a.F_QLTK_SUPPLIER,a.F_QLTK_YEAR,a.F_QLTK_MONTH,a.FMAINBOOKSTDCURRID,b.FEntryID,b.F_QLTK_PURCHASER from dev_SupplierAPB a
                                        inner join dev_SupplierAPBENTITY b on b.FID = a.FID
                                        where F_QLTK_year='" + lastMonthYear + "' and F_QLTK_month='" + lastMonthMonth + "'");
                DataTable dt24 = DBUtils.ExecuteDataSet(ctx, sql13).Tables[0];
                if (dt24.Rows.Count > 0)
                {
                    for (int i = 0; i < dt24.Rows.Count; i++)
                    {
                        string F_QLTK_OrgId = dt24.Rows[i]["F_QLTK_OrgId"].ToString();//组织   
                        string F_QLTK_Supplier = dt24.Rows[i]["F_QLTK_Supplier"].ToString();//供应商
                        string FEntryID = dt24.Rows[i]["FEntryID"].ToString();//FEntryID
                        string F_QLTK_PURCHASER = dt24.Rows[i]["F_QLTK_PURCHASER"].ToString();//员工
                        string FMAINBOOKSTDCURRID = dt24.Rows[i]["FMAINBOOKSTDCURRID"].ToString();//币别

                        //计算应付单-本期应付发生额（财务应付）
                        string sqls = string.Format(@"/*dialect*/select FSETTLEORGID,FSUPPLIERID,FCURRENCYID,F_DEV_PURCHASER,sum(FALLAMOUNTFOR) as sum from T_AP_PAYABLE a
                                    where  FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) and FSetAccountType='2'  and FDOCUMENTSTATUS='C'
                                    and FSETTLEORGID='" + F_QLTK_OrgId + "' and FSUPPLIERID='" + F_QLTK_Supplier + "' and F_DEV_PURCHASER='" + F_QLTK_PURCHASER + "' and FCURRENCYID='" + FMAINBOOKSTDCURRID + "' GROUP BY FSUPPLIERID,FSETTLEORGID,FCURRENCYID,F_DEV_PURCHASER");
                        DataTable dts = DBUtils.ExecuteDataSet(ctx, sqls).Tables[0];
                        if (dts.Rows.Count > 0)
                        {
                            DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_ZAMOUNTZAP='" + dts.Rows[0]["sum"] + "' where FEntryID='" + FEntryID + "'");
                        }

                        //计算应付单-本期应付发生额（财务应付）
                        string sql2 = string.Format(@"/*dialect*/select FSETTLEORGID,FSUPPLIERID,FCURRENCYID,F_DEV_PURCHASER,sum(FALLAMOUNTFOR) as sum from T_AP_PAYABLE a
                                    where 
                                    FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))  
                                    AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) and FSetAccountType in( '3','1') and FDOCUMENTSTATUS='C'
                                    and FSETTLEORGID='" + F_QLTK_OrgId + "' and FSUPPLIERID='" + F_QLTK_Supplier + "' and F_DEV_PURCHASER='" + F_QLTK_PURCHASER + "' and FCURRENCYID='"+ FMAINBOOKSTDCURRID + "' GROUP BY FSUPPLIERID,FSETTLEORGID,FCURRENCYID,F_DEV_PURCHASER");
                        DataTable dt3 = DBUtils.ExecuteDataSet(ctx, sql2).Tables[0];
                        if (dt3.Rows.Count > 0)
                        {
                            DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_EAMOUNTCAP='" + dt3.Rows[0]["sum"] + "' where FEntryID='" + FEntryID + "'");
                        }
                        //计算其他应付单-其他应付总金额
                        string sql3 = string.Format(@"/*dialect*/select FSETTLEORGID,FCONTACTUNIT,FCURRENCYID,F_RDLV_APPLY,sum(FTOTALAMOUNT) as sum from  T_AP_OTHERPAYABLE 
                                        where FCONTACTUNITTYPE='BD_Supplier' and
                                        FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))  
                                        AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))  and FDOCUMENTSTATUS='C'
                                        and FSETTLEORGID='" + F_QLTK_OrgId + "' and FCONTACTUNIT='" + F_QLTK_Supplier + "'  and F_RDLV_APPLY='" + F_QLTK_PURCHASER + "' and FCURRENCYID='"+ FMAINBOOKSTDCURRID + "' GROUP BY FCONTACTUNIT, FSETTLEORGID,FCURRENCYID, F_RDLV_APPLY; ");
                        DataTable dt4 = DBUtils.ExecuteDataSet(ctx, sql3).Tables[0];
                        if (dt4.Rows.Count > 0)
                        {
                            DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_EOAPAMOUNT='" + dt4.Rows[0]["sum"] + "' where FEntryID='" + FEntryID + "'");
                        }
                        //计算付款单-付款总金额
                        string sql4 = string.Format(@"/*dialect*/select FSETTLEORGID,FCONTACTUNIT,FCURRENCYID,F_DEV_PURCHASER,sum(FPAYTOTALAMOUNT) as sum from T_AP_PAYBILL 
                                        where FCONTACTUNITTYPE='BD_Supplier' and
                                        FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))  
                                        AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))   and FDOCUMENTSTATUS='C'
                                        and FSETTLEORGID='" + F_QLTK_OrgId + "' and FCONTACTUNIT='" + F_QLTK_Supplier + "' and F_DEV_PURCHASER='" + F_QLTK_PURCHASER + "' and FCURRENCYID='"+ FMAINBOOKSTDCURRID + "'  GROUP BY FCONTACTUNIT, FSETTLEORGID,FCURRENCYID, F_DEV_PURCHASER");
                        DataTable dt5 = DBUtils.ExecuteDataSet(ctx, sql4).Tables[0];
                        if (dt5.Rows.Count > 0)
                        {
                            DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_EPAYAMOUNT='" + dt5.Rows[0]["sum"] + "' where FEntryID='" + FEntryID + "'");
                        }

                        //计算付款退款单-付款退款总金额
                        string sql5 = string.Format(@"/*dialect*/select FSETTLEORGID,FCONTACTUNIT,FCURRENCYID,F_DEV_PURCHASER,sum(FREFUNDAMOUNT_H) as sum from T_AP_REFUNDBILL
                                    where FCONTACTUNITTYPE='BD_Supplier' and
                                    FCREATEDATE >= DATEADD(MONTH, -1, DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)))  
                                    AND FCREATEDATE < DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))   and FDOCUMENTSTATUS='C'
                                    and FSETTLEORGID='" + F_QLTK_OrgId + "' and FCONTACTUNIT='" + F_QLTK_Supplier + "' and F_DEV_PURCHASER='" + F_QLTK_PURCHASER + "' and FCURRENCYID='"+ FMAINBOOKSTDCURRID + "' GROUP BY FCONTACTUNIT, FSETTLEORGID,FCURRENCYID, F_DEV_PURCHASER; ");
                        DataTable dt6 = DBUtils.ExecuteDataSet(ctx, sql5).Tables[0];
                        if (dt6.Rows.Count > 0)
                        {
                            DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_EPAYRETURNAMOUNT='" + dt6.Rows[0]["sum"] + "' where FEntryID='" + FEntryID + "'");
                        }
                        //更新期末总余额=期初应付金额+财务应付+其他应付-付款+付款退款
                        DBUtils.Execute(ctx, "update dev_SupplierAPBENTITY set F_QLTK_EENDAMOUNT= F_QLTK_EINITAMOUNT+F_QLTK_EAMOUNTCAP+F_QLTK_EOAPAMOUNT-F_QLTK_EPAYAMOUNT+F_QLTK_EPAYRETURNAMOUNT  where  FEntryID='" + FEntryID + "'");
                    }
                }
            }
        }
    }
}
